from flask import Flask, request, jsonify
import pymysql
import csv
from io import StringIO

app = Flask(__name__)

# 数据库配置
db = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='bank',
    cursorclass=pymysql.cursors.DictCursor
)

# 1. 客户管理
@app.route('/api/customers', methods=['GET', 'POST', 'PUT', 'DELETE'])
def manage_customers():
    cursor = db.cursor()
    if request.method == 'GET':
        # 查询所有客户
        cursor.execute("SELECT * FROM userInfo")
        return jsonify(cursor.fetchall())
    elif request.method == 'POST':
        # 新增客户(触发器会自动开户)
        data = request.json
        sql = """INSERT INTO userInfo (customerName, PID, telephone, address) 
                 VALUES (%s, %s, %s, %s)"""
        cursor.execute(sql, (data['name'], data['pid'], data['phone'], data.get('address')))
        db.commit()
        return jsonify({"status": "success", "message": "客户创建成功，已自动开户"})

# 2. 银行卡管理
@app.route('/api/cards', methods=['GET'])
def get_cards():
    cursor = db.cursor()
    cursor.execute("SELECT * FROM card_summary_view")
    return jsonify(cursor.fetchall())

# 3. 存取款业务
@app.route('/api/transaction', methods=['POST'])
def transaction():
    data = request.json
    cursor = db.cursor()
    try:
        # 直接更新余额，触发器会自动记录交易
        sql = "UPDATE cardInfo SET balance = balance + %s WHERE cardID = %s"
        cursor.execute(sql, (data['amount'], data['card_id']))
        db.commit()
        return jsonify({"status": "success"})
    except Exception as e:
        db.rollback()
        return jsonify({"error": str(e)}), 500

# 4. 转账业务(调用存储过程)
@app.route('/api/transfer', methods=['POST'])
def transfer():
    data = request.json
    cursor = db.cursor()
    try:
        cursor.callproc('transfer_money', 
            (data['from_card'], data['to_card'], data['amount'], None))
        cursor.execute("SELECT @result")
        result = cursor.fetchone()['@result']
        db.commit()
        return jsonify({"status": "success", "message": result})
    except Exception as e:
        db.rollback()
        return jsonify({"error": str(e)}), 500

# 5. 导出CSV报表
@app.route('/api/export/<table_name>', methods=['GET'])
def export_csv(table_name):
    cursor = db.cursor()
    cursor.execute(f"SELECT * FROM {table_name}")
    data = cursor.fetchall()
    
    # 生成CSV
    output = StringIO()
    writer = csv.DictWriter(output, fieldnames=data[0].keys())
    writer.writeheader()
    writer.writerows(data)
    
    return jsonify({"csv": output.getvalue()})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5415, debug=True)  # 改为 8000 端口
